<?php
/**
 * This is an example code that shows how you can load Handsontable data from server using PHP with PDO (SQLite).
 * This code is not intended to be maximally efficient nor safe. It is for demonstrational purposes only.
 * Changes and more examples in different languages are welcome.
 *
 * Copyright 2012, Marcin Warpechowski
 * Licensed under the MIT license.
 * http://warpech.github.com/jquery-handsontable/
 */

require_once('functions.php');


function sqlSelect($discription, $cols) {
  $result = array();
  foreach ($cols as $col) {
    if (isset($discription[$col]['sql'])) {
      array_push($result, $discription[$col]['sql']);
    } else {
      array_push($result, $col);
    }
  }
  return implode(",", $result);
}

try {
  //open the database
  $db = getConnection();

  $kleuren = array();
  foreach($db->query("select kleur from `groep`") as $row) {
      array_push($kleuren, $row['kleur']);
  }

  
  $all = array("id","roepnaam","tussenvoegsel","achternaam","geboortedatum","straat","huisnummer","toevoeging","postcode","plaats","email","telefoon","mobiel1","mobiel2",
    "verzekeraar","polisnummer","huisarts","huisartsTelefoon","gezondheid","dieten","overig","bijzonderheden","geslacht","orderId","barcode", "polsband");
  
  $discription = array(
    "aid" => array("name" =>"id", "width" => 50, "column" => array("data" => "id", "readOnly" => "true"), "sql" => "a.id as id"), 
    "pid" => array("name" =>"id", "width" => 50, "column" => array("data" => "id", "readOnly" => "true"), "sql" => "p.id as id"), 
    "id" => array("name" =>"id", "width" => 50, "column" => array("data" => "id", "readOnly" => "true"), "sql" => "id"), 
    "roepnaam" => array("name" =>"roepnaam", "width" => 100, "column" => array("data" => "roepnaam")),
    "tussenvoegsel" => array("name" =>"", "width" => 60, "column" => array("data" => "tussenvoegsel")),
    "achternaam" => array("name" =>"achternaam", "width" => 100, "column" => array("data" => "achternaam")),
    "roepnaamX" => array("name" =>"roepnaam", "width" => 100, "column" => array("data" => "roepnaam", "readOnly" => "true"), "sql" => "roepnaam"),
    "tussenvoegselX" => array("name" =>"", "width" => 60, "column" => array("data" => "tussenvoegsel", "readOnly" => "true"), "sql" => "tussenvoegsel"),
    "achternaamX" => array("name" =>"achternaam", "width" => 100, "column" => array("data" => "achternaam", "readOnly" => "true"), "sql" => "achternaam"),
    "geboortedatum" => array("name" =>"geboortedatum", "width" => 100, "column" => array("data" => "geboortedatum", "type" => "date", "dateFormat" => "yy-mm-dd", "yearRange" => "1995:2010")),
    "straat" => array("name" =>"straat", "width" => 150, "column" => array("data" => "straat")),
    "huisnummer" => array("name" =>"nr.", "width" => 50, "column" => array("data" => "huisnummer")),
    "toevoeging" => array("name" =>"toe.", "width" => 50, "column" => array("data" => "toevoeging")),
    "postcode" => array("name" =>"postcode", "width" => 60, "column" => array("data" => "postcode")),
    "plaats" => array("name" =>"plaats", "width" => 100, "column" => array("data" => "plaats")),
    "email" => array("name" =>"email", "width" => 250, "column" => array("data" => "email")),
    "telefoon" => array("name" =>"telefoon", "width" => 100, "column" => array("data" => "telefoon")),
    "mobiel1" => array("name" =>"mobiel1", "width" => 100, "column" => array("data" => "mobiel1")),
    "mobiel2" => array("name" =>"mobiel2", "width" => 100, "column" => array("data" => "mobiel2")),
    "verzekeraar" => array("name" =>"verzekeraar", "width" => 100, "column" => array("data" => "verzekeraar")),
    "polisnummer" => array("name" =>"polisnummer", "width" => 100, "column" => array("data" => "polisnummer")),
    "huisarts" => array("name" =>"huisarts", "width" => 100, "column" => array("data" => "huisarts")),
    "huisartsTelefoon" => array("name" =>"huisarts telefoon", "width" => 100, "column" => array("data" => "huisartsTelefoon")),
    "gezondheid" => array("name" =>"gezondheid", "width" => 500, "column" => array("data" => "gezondheid")),
    "dieten" => array("name" =>"dieten", "width" => 500, "column" => array("data" => "dieten")),
    "overig" => array("name" =>"overig", "width" => 500, "column" => array("data" => "overig")),
    "bijzonderheden" => array("name" =>"bijzonderheden", "width" => 500, "column" => array("data" => "bijzonderheden")),
    "geslacht" => array("name" =>"geslacht", "width" => 100, "column" => array("data" => "geslacht")),
    "orderId" => array("name" =>"orderId", "width" => 50, "column" => array("data" => "orderId", "readOnly" => "true")),
    "barcode" => array("name" =>"barcode", "width" => 100, "column" => array("data" => "barcode", "readOnly" => "true")),   
    "kleur" => array("name" =>"groeps kleur", "width" => 125, "column" => array("data" => "kleur", "readOnly" => "true", "type"=>"autocomplete", "source"=>$kleuren)),    
    "kleur_e" => array("name" =>"groeps kleur", "width" => 125, "column" => array("data" => "kleur", "type"=>"autocomplete", "source"=>$kleuren), "sql" => "kleur"), 
    "opmerking" => array("name" =>"opmerking", "width" => 500, "column" => array("data" => "opmerking")),   
    "ma" => array("name" =>"ma", "width" => 50, "column" => array("data" => "ma", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(ma=1,\"true\",\"false\") as ma"),
    "di" => array("name" =>"di", "width" => 50, "column" => array("data" => "di", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(di=1,\"true\",\"false\") as di"),    
    "wo" => array("name" =>"wo", "width" => 50, "column" => array("data" => "wo", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(wo=1,\"true\",\"false\") as wo"),     
    "do" => array("name" =>"do", "width" => 50, "column" => array("data" => "do", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(do=1,\"true\",\"false\") as do"),
    "vr" => array("name" =>"vr", "width" => 50, "column" => array("data" => "vr", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(vr=1,\"true\",\"false\") as vr"),    
    "za" => array("name" =>"za", "width" => 50, "column" => array("data" => "za", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(za=1,\"true\",\"false\") as za"),    
    "zo" => array("name" =>"zo", "width" => 50, "column" => array("data" => "zo", "type" => "checkbox", "readOnly" => "true"), "sql" => "if(zo=1,\"true\",\"false\") as zo"),   
    "polsband" => array("name" =>"polsband", "width" => 50, "column" => array("data" => "polsband")),
  );
  

  if(!carsTableExists($db)){
      resetCarsTable($db);
  }
  
  
  error_log("load a ".$_GET['a']."\n", 3, "/tmp/php.log"); 

  
  
  switch($_GET['a']) {
    case 'dieten':
      $cols = array("id", "roepnaam", "tussenvoegsel", "achternaam", "dieten");
      $sql = 'SELECT '.implode(",", $cols).' FROM persoon AS p ORDER BY id ASC LIMIT 1000';
      break;
      
    case 'overig':
      $cols = array("id", "roepnaam", "tussenvoegsel", "achternaam", "overig");
      $sql = 'SELECT '.implode(",", $cols).' FROM persoon AS p ORDER BY id ASC LIMIT 1000';
      break;
    
    case 'gezondheid':
      $cols = array("id", "roepnaam", "tussenvoegsel", "achternaam", "verzekeraar","polisnummer","huisarts","huisartsTelefoon","gezondheid");
      $sql = 'SELECT '.implode(",", $cols).' FROM persoon AS p ORDER BY id ASC LIMIT 1000';
      break;
      
    case 'deelnemers':
      $cols = array("id","roepnaam","tussenvoegsel","achternaam","geboortedatum","straat","huisnummer","toevoeging","postcode","plaats","email","telefoon","mobiel1","mobiel2");
      $sql = 'SELECT '.implode(",", $cols).' FROM persoon AS p ORDER BY id ASC LIMIT 1000';
      break;
      
    case 'groepsindeling': 
      $cols = array("pid", "kleur", "roepnaam", "tussenvoegsel", "achternaam", "bijzonderheden");
      $sql = 'SELECT '.sqlSelect($discription, $cols).' FROM persoon AS p LEFT JOIN groep AS g ON p.groepId = g.id ORDER BY id ASC LIMIT 1000';
      break;

    case 'kleuren':
      $cols = array("id", "kleur_e", "barcode",	"opmerking");
      $sql = 'SELECT '.sqlSelect($discription, $cols).' FROM groep ORDER BY id ASC LIMIT 1000';
    break;

    case 'presentie':
      $cols = array("aid", "roepnaamX", "tussenvoegselX", "achternaamX", "ma", "di", "wo", "do", "vr", "za", "zo");
      $sql = 'SELECT '.sqlSelect($discription, $cols).' FROM persoon AS p LEFT JOIN presentie AS a ON p.polsband = a.polsband ORDER BY roepnaam, achternaam ASC LIMIT 1000';
    break;

    default:
      $cols = $all;
      $sql = 'SELECT '.implode(",", $cols).' FROM persoon ORDER BY id ASC LIMIT 1000';
  }
  
  

  error_log("load sql ".$sql."\n", 3, "/tmp/php.log"); 
  $result = $db->prepare($sql);
  $result->execute();

  
  $data = $result->fetchAll(PDO::FETCH_ASSOC);

  $rows = array();
  foreach($data as $row) {
    $cels = array();
    foreach($row as $cel) {
      array_push($cels, $cel);
    }
    array_push($rows, $cels);
  }

  $colHeaders = array();
  $columns = array();
  $colWidths = array();
  foreach ($cols as $col) {
    array_push($colWidths, $discription[$col]['width']);
    array_push($colHeaders, $discription[$col]['name']);
    array_push($columns, $discription[$col]['column']);
  }

  
  $out = array(
    "data" => $data,
    "colWidths" => $colWidths,
    "colHeaders" => $colHeaders,
    "columns" => $columns
  );
  

  

  echo json_encode($out);
  
  // close the database connection
  closeConnection($db);
} catch (PDOException $e) {
  print 'Exception : ' . $e->getMessage();
}
?>
